const moment = require('moment');
const Excel = require('../lib/excel');
const Customer = require('../../app/models/customer');
const Promise = require('bluebird');

const Ticket = require('../../app/models/ticket');
class Finance {
    constructor(types, startTime, endTime) {
        this._types = this.typesToMap(types);
        this.setTime(startTime, endTime);
    }

    initExcel() {
        this._excel = new Excel();
        const columns = [
            { header: '票券类型', key: 'name', width: 20 },
            { header: '总数', key: 'total', width: 10, outlineLevel: 1 },
            { header: '待审核', key: 'pending', width: 10, outlineLevel: 1 },
            { header: '库存', key: 'stock', width: 10 },
            { header: '备售', key: 'ready', width: 10, outlineLevel: 1 },
            { header: '未使用', key: 'unused', width: 15, outlineLevel: 1 },
            { header: '已使用', key: 'used', width: 15, outlineLevel: 1 },
            { header: '已核销', key: 'completed', width: 15, outlineLevel: 1 },
        ];
        this._excel.buildSheet('票券汇总表', columns);
    }

    typesToMap(data) {
        const map = {};
        data.map((item) => {
            if(!item.id && item.get('id')) {
                item.name = item.parent.name + item.name;
                map[item.get('id')] = item;
            }
            map[item.id] = item;
        });
        return map;
    }
    offsetTime(time) {
        return time - (8 * 60 * 60 * 1000);
    }
    setTime(startTime, endTime) {
        if(!startTime && !endTime) {
            const date = new Date();
            date.setDate(1);

            this._end_time = new Date(moment(date).format('YYYY-MM-DD'));
            this._start_time = new Date(date.setMonth(date.getMonth() - 1));
            this._end_time = new Date(this.offsetTime(this._end_time.getTime()));
            this._start_time = new Date(this.offsetTime(this._start_time.getTime()));
        }
        else {
            this._end_time = endTime;
            this._start_time = startTime;
        }

        this._results = [];
        this.initExcel();
    }
    total() {
        const results = {};

        return Ticket.query(qb => {
            qb.select('type_id', 'status');
            qb.where('deleted', Ticket.NOT_DELETED);
            qb.count('id as count');
            qb.groupBy('status', 'type_id');
        })
        .fetchAll()
        .then(tickets => {
            return tickets.models;
        })
        .map(ticket => {
            if(!results[ticket.get('type_id')]) {
                results[ticket.get('type_id')] = {
                    stock: 0,
                    pending: 0,
                    ready: 0,
                    unused: 0,
                    used: 0,
                    completed: 0
                };
            }

            if(ticket.get('status') === 0) {
                results[ticket.get('type_id')].stock = ticket.get('count');
            }
            if(ticket.get('status') === 1) {
                results[ticket.get('type_id')].pending = ticket.get('count');
            }
            if(ticket.get('status') === 2) {
                results[ticket.get('type_id')].ready = ticket.get('count');
            }
            if(ticket.get('status') === 3) {
                results[ticket.get('type_id')].unused = ticket.get('count');
            }
            if(ticket.get('status') === 4) {
                results[ticket.get('type_id')].used = ticket.get('count');
            }
            if(ticket.get('status') === 5) {
                results[ticket.get('type_id')].completed = ticket.get('count');
            }
        })
        .then(() => {
            return Object.keys(results);
        })
        .map(key => {
            if(this._types[key]) {
                results[key].name = this._types[key] ? this._types[key].name : name;
                results[key].total = results[key].stock +
                    results[key].pending +
                    results[key].ready +
                    results[key].unused +
                    results[key].used +
                    results[key].completed;
                this._excel.addRow(results[key]);
            }
        })
        .then(() => {
            return this.expired();
        })
        .then(() => {
            return this.countByCustomer();
        });
    }

    expired() {
        const columns = [
            { header: '票券类型', key: 'name', width: 20 },
            { header: '未使用', key: 'total', width: 10 },
            { header: '已过期', key: 'expried', width: 10, outlineLevel: 1 },
            { header: '即将过期(15天)', key: 'exprieding', width: 20, outlineLevel: 1 },
        ];
        this._excel.buildSheet('票券表过期表', columns);
        const results = [];

        return Promise.try(() => {
            return Object.keys(this._types);
        })
        .map(key => {
            results[key] = {
                name: this._types[key].name,
                total: 0,
                expried: 0,
                exprieding: 0
            };

            return Ticket.query(qb => {
                qb.where('status', Ticket.UNUSED);
                qb.where('type_id', key);
                qb.where('end_time', '<', this._start_time);
                qb.where('deleted', Ticket.NOT_DELETED);
                qb.count('id as count');
            })
            .fetch()
            .then(count => {
                results[key].expired = count.get('count');
            })
            .then(() => {
                return Ticket.query(qb => {
                    qb.where('status', Ticket.UNUSED);
                    qb.where('type_id', key);
                    qb.where('end_time', '<', new Date(this._start_time.getTime() + 15 * 24 * 60 * 60 * 1000));
                    qb.where('deleted', Ticket.NOT_DELETED);
                    qb.count('id as count');
                })
                .fetch();
            })
            .then(count => {
                results[key].expireding = count.get('count');
            })
            .then(() => {
                return Ticket.query(qb => {
                    qb.where('status', Ticket.UNUSED);
                    qb.where('type_id', key);
                    qb.where('deleted', Ticket.NOT_DELETED);
                    qb.count('id as count');
                })
                .fetch();
            })
            .then(count => {
                results[key].total = count.get('count');
                this._excel.addRow(results[key]);
            });
        });
    }
    countByCustomer() {
        const columns = [
            { header: '客户名称', key: 'customer_name', width: 20 },
            { header: '票券类型', key: 'name', width: 20 },
            { header: '未使用', key: 'total', width: 10 },
            { header: '已过期', key: 'expried', width: 10, outlineLevel: 1 },
            { header: '即将过期(15天)', key: 'exprieding', width: 20, outlineLevel: 1 },
        ];
        this._excel.buildSheet('票券客户票券表', columns);
        const results = {};

        return Customer.where({

        })
        .fetchAll()
        .then(customers => {
            return customers.models;
        })
        .map(customer => {
            return Promise.try(() => {
                return Object.keys(this._types);
            })
            .map(key => {
                results[key] = {
                    name: this._types[key].name,
                    customer_name: customer.get('name'),
                    total: 0,
                    expried: 0,
                    exprieding: 0
                };

                return Ticket.query(qb => {
                    qb.where('status', Ticket.UNUSED);
                    qb.where('type_id', key);
                    qb.where('customer_id', customer.get('id'));
                    qb.where('end_time', '<', this._start_time);
                    qb.where('deleted', Ticket.NOT_DELETED);
                    qb.count('id as count');
                })
                .fetch()
                .then(count => {
                    results[key].expired = count.get('count');
                })
                .then(() => {
                    return Ticket.query(qb => {
                        qb.where('status', Ticket.UNUSED);
                        qb.where('type_id', key);
                        qb.where('customer_id', customer.get('id'));
                        qb.where('end_time', '<', new Date(this._start_time.getTime() + 15 * 24 * 60 * 60 * 1000));
                        qb.where('deleted', Ticket.NOT_DELETED);
                        qb.count('id as count');
                    })
                    .fetch();
                })
                .then(count => {
                    results[key].expireding = count.get('count');
                })
                .then(() => {
                    return Ticket.query(qb => {
                        qb.where('status', Ticket.UNUSED);
                        qb.where('type_id', key);
                        qb.where('customer_id', customer.get('id'));
                        qb.where('deleted', Ticket.NOT_DELETED);
                        qb.count('id as count');
                    })
                    .fetch();
                })
                .then(count => {
                    results[key].total = count.get('count');
                    this._excel.addRow(results[key]);
                })
                // .then(() => {
                //     this._excel._worksheet.mergeCells(`A${i * 3}`);
                // });
            });
        });
    }
};

module.exports = Finance;

